﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// User_Control 的摘要说明
/// </summary>aa
public class User_Control
{
	public User_Control()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    public static bool User_Login(string param1, string param2,out string schoolFlag)
    {
        try
        {
            SqlParameter[] parms = new SqlParameter[]{			
			    new SqlParameter("@param1",SqlDbType.NVarChar,20),
			    new SqlParameter("@param2",SqlDbType.NVarChar,20),
                new SqlParameter("@returnValue",SqlDbType.Bit),
                new SqlParameter("@SchoolFlag",SqlDbType.Bit),
			};
            parms[0].Value = param1.Trim();
            parms[1].Value = param2.Trim();
            parms[2].Direction = ParameterDirection.ReturnValue;
            parms[3].Direction = ParameterDirection.Output;
            Dbase.ExecuteNonQuery(CommandType.StoredProcedure, "UserLogin", parms);
            schoolFlag = parms[3].Value.ToString();
            if (parms[2].Value.ToString() == "1")
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception ex)
        {
            schoolFlag = "0";
            return false;
        }
        
    }
    public static bool insertNewStudentInfo(string name, string idCard, string sex,string studId, string from, string deptName, string className, string enterYear,string tel)
    {
        try
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@name",SqlDbType.NVarChar,10),
                new SqlParameter("@idCard",SqlDbType.VarChar,18),
                new SqlParameter("@sex",SqlDbType.Char,2),
                new SqlParameter("@studId",SqlDbType.VarChar,20),
                new SqlParameter("@from",SqlDbType.NVarChar,20),
                new SqlParameter("@deptName",SqlDbType.NVarChar,20),
                new SqlParameter("@className",SqlDbType.VarChar,20),
                new SqlParameter("@enterYear",SqlDbType.Char,4),
                new SqlParameter("@tel",SqlDbType.NVarChar,50),
            };
            parms[0].Value = name;
            parms[1].Value = idCard;
            parms[2].Value = sex;
            parms[3].Value = studId;
            parms[4].Value = from;
            parms[5].Value = deptName;
            parms[6].Value = className;
            parms[7].Value = enterYear;
            parms[8].Value = tel;
            Dbase.ExecuteNonQuery(CommandType.StoredProcedure, "insertNewAccount", parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    /// <summary>
    /// 不存在的学生插入到学生和用户中
    /// </summary>
    /// <param name="studId"></param>
    /// <returns></returns>
    public static string insertStudentInfo(string studId)
    {
        try
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@StudID",SqlDbType.VarChar,20),
                new SqlParameter("@rtn",SqlDbType.VarChar,20)
            };
            parms[0].Value = studId.Trim();
            parms[1].Direction = ParameterDirection.Output;
            Dbase.ExecuteNonQuery(CommandType.StoredProcedure, "InsertStudentFromView", parms);
            
            return parms[2].Value.ToString();
        }
        catch (Exception ex)
        {
            return ex.ToString();
        }

    }
    /// <summary>
    /// 从学生信息表中获取学生信息
    /// </summary>
    /// <param name="studId"></param>
    /// <returns></returns>
    public static DataTable getStudentInfoByStudId(string studId)
    {
        string cmdText = "select * from StudentInfo where StudID=@studId";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@studId",SqlDbType.VarChar,20)
        };
        parms[0].Value = studId.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 通过学号得到学生信息(从view获取)
    /// </summary>
    /// <returns></returns>
    public static DataTable getStudentInfoInViewByStudId(string studId)
    {
        string cmdText = "select * from View_AllStudent where StudID=@studId";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@studId",SqlDbType.VarChar,20)
        };
        parms[0].Value = studId.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 通过身份证号得到学生信息
    /// </summary>
    /// <param name="studIdCard"></param>
    /// <returns></returns>
    public static DataTable getStudInfoByIdCard(string studIdCard)
    {
        string cmdText = "select * from StudentInfo where StudIdcard=@StudIdcard";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@StudIdcard",SqlDbType.VarChar,18) 
        };
        parms[0].Value = studIdCard.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    public static DataTable getStudInfoInViewByIdCard(string idCard)
    {
        string cmdText = "select * from View_AllStudent where IDCard=@idCard";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@idCard",SqlDbType.VarChar,50)
        };
        parms[0].Value = idCard.Trim();
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 是否存在用户名
    /// </summary>
    /// <param name="userID"></param>
    /// <returns>返回True为存在，返回False为不存在</returns>
    public static bool UserExist(string userID)
    {
        string cmdText = "select * from userInfo where UserID = @UserID ";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@UserID",SqlDbType.NVarChar,25) 
        };
        parms[0].Value = userID.Trim();
        DataTable dt = (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
        return dt.Rows.Count > 0;
    }
    /// <summary>
    /// 得到用户信息
    /// </summary>
    /// <param name="userName"></param>
    /// <param name="userId"></param>
    /// <returns></returns>
    public static DataTable getUserInfoByLoginMsg(string userName, string userId)
    {
        string cmdText = "select * from UserInfo where UserName=@userName and StudIdcard=@StudIdcard";
        SqlParameter[] parms = new SqlParameter[]{
            new SqlParameter("@userName",SqlDbType.NVarChar,20),
            new SqlParameter("@StudIdcard",SqlDbType.VarChar,20)
        };
        parms[0].Value = userName.Trim();
        parms[1].Value = userId.Trim();

        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, parms);
    }
    /// <summary>
    /// 得到注册学生表
    /// </summary>
    /// <returns></returns>
    public static DataTable getNotAccessStudentInfo()
    {
        string cmdText = "select * from StudentInfo where StudState=1";
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, null);
    }
    /// <summary>
    /// 更新学生状态
    /// </summary>
    /// <param name="state"></param>
    /// <param name="idcard"></param>
    /// <returns></returns>
    public static bool updateStudentStateByIdcard(string state,string idcard)
    {
        try
        {
            string cmdText = "update StudentInfo set StudState=@state where StudIdcard=@idcard";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@state",SqlDbType.TinyInt),
                new SqlParameter("@idcard",SqlDbType.VarChar,20)
            };
            parms[0].Value = state;
            parms[1].Value = idcard;
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
    /// <summary>
    /// 得到所有学生信息
    /// </summary>
    /// <returns></returns>
    public static DataTable getAllStudInfo()
    {
        string cmdText = "select * from StudentInfo";
        return (DataTable)Dbase.GetDataTable(CommandType.Text, cmdText, null);
    }

    public static bool deleteStudentInfoByIdcard(string idcard)
    {
        try
        {
            string cmdText = "delect StudentInfo where StudIdcard=@idcard";
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@idcard",SqlDbType.VarChar,20)
            };
            Dbase.ExecuteNonQuery(CommandType.Text, cmdText, parms);
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }
}